package code.lewdy.db;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import lottery.BetwayManage;
import code.lewdy.reflect.MethodUtil;

/**
 * Microsoft Sql Server辅助类
 * 
 * @author liuding,2012-5-8 $LastChangedDate$ $Author$
 */
public class MSSQLUtil {
	public static void main(String[] args) {
		// System.out.println(JdbcUtil.executeUpdate("delete from BetRecord where id = 25"));
		// System.out.println(delete("lottery.BetRecord", 27L));
		//List<BetwayManage> bms = (List<BetwayManage>) list("lottery.BetwayManage", "where id < 100 order by id ASC", 4, 3);
		//List<BetwayManage> bms = (List<BetwayManage>) listAll("lottery.BetwayManage", "where id < 100 order by id ASC");
		//System.out.println(bms.size()+bms.get(0).getBetwayName());
		//System.out.println(list("com.lt.member.Member", "where fatherId = 20 order by id DESC", 1, 5).size());
		System.out.println(listAll("com.lt.member.Member", "select * from Member").size());
	}

	/**
	 * 删除一个对象
	 * @param classPath
	 * @param objId
	 * @return 更新数目
	 * @author liuding,2012-5-8
	 */
	public static int delete(String classPath, Long objId) {
		int results = 0;
		String table = classPath.contains(".") ? classPath.substring(classPath
				.lastIndexOf(".") + 1, classPath.length()) : classPath;
		results = JdbcTemple.executeUpdate("delete from " + table
				+ " where id = " + objId);
		return results;
	}
    
    /**通过SQL查询一个唯一结果并封装成实例
     * @param classPath
     * @param sql
     * @return
     * @author liuding,2012-5-17
     */
    public static Object getEntityBySQL(String classPath, String sql){
    	String table = classPath.contains(".") ? classPath.substring(classPath
				.lastIndexOf(".") + 1, classPath.length()) : classPath;
    	if(sql.startsWith("where") || sql.startsWith("order")){
    		sql = "select * from "+table+" "+sql;
    	}
        Object obj = null;
        Method setMs[] = MethodUtil.getBeanMethods(classPath, "set");
        String field = "";
        String paramType = "String";
        Connection conn = JdbcTemple.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Class c = null;
        try
        {
            c= Class.forName(classPath);
        }
        catch (ClassNotFoundException e2)
        {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        }
        try {
            while (rs.next()) {
              try
                {
                    obj = c.newInstance();
                }
                catch (Exception e1)
                {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                
                for(Method m : setMs){
                    field = m.getName().length()>3 ? m.getName().substring(3, m.getName().length()).toLowerCase()
                                : m.getName().replace("set", "");
                    if(m.getParameterTypes().length > 0){
                        paramType = m.getParameterTypes()[0]+"";
                        if(paramType.contains("int")){
                            try
                            {
                                m.invoke(obj, rs.getInt(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("double")){
                            try
                            {
                                m.invoke(obj, rs.getDouble(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("float")){
                            try
                            {
                                m.invoke(obj, rs.getFloat(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("String")){
                            try
                            {
                                if(rs.getString(field) != null){
                                    m.invoke(obj, rs.getString(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Date")){
                            
                            try
                            {
                                if(rs.getDate(field) != null){
                                    m.invoke(obj, rs.getTimestamp(field));
                                //    m.invoke(obj, rs.getDate(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Long")){
                            try
                            {
                            	m.invoke(obj, rs.getLong(field));  
                            }catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Double")){
                        	try
                            {
                            	m.invoke(obj, rs.getDouble(field));  
                            }catch (Exception e)
                            {
                                e.printStackTrace();
                            }
                        }else{
                            System.out.println("MSSQLUtil.getEntityBySQL()=>未处理的参数类型:"+paramType);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return obj;
    }

	/**
	 * @param classPath
	 * @param condition
	 * @param page
	 *            页码，第一页为1
	 * @param pageSize
	 * @return
	 */
	public static List<?> list(String classPath, String condition, int page,
			int pageSize) {
		List list = new ArrayList();
		if(classPath == null)
			return list;
        Method setMs[] = MethodUtil.getBeanMethods(classPath, "set");
        String field = "";
        String paramType = "String";
        condition = condition!=null ? condition : "";
		String table = classPath.contains(".") ? classPath.substring(classPath
				.lastIndexOf(".") + 1, classPath.length()) : classPath;
		String pk = "id"; //主键
        String orderColumn = pk;
        String order = "DESC";
        String order2 = "ASC";
        String order3 = "ASC";
		int max = page * pageSize;
        //ORDER BY " + pk + " DESC
		if(!condition.toUpperCase().contains("ORDER")){
            condition += "ORDER BY " + pk + " ASC";
            order2 = "DESC";
            order3 = "DESC";
        }
        if(condition.toUpperCase().contains("ASC")){
            order2 = "DESC";
            order3 = "DESC";
        }
        if(condition.toUpperCase().contains("DESC")){
            order2 = "ASC";
            order3 = "ASC";
        }
		String sql = "SELECT * FROM "+
            "(SELECT TOP " + pageSize + " * FROM" + 
            "(SELECT TOP " + max + " * FROM " + 
            "(SELECT TOP " + max + " * FROM "+table+ " "+condition+" ) t1 "+ 
            "ORDER BY " + pk + " "+order2+") t2 " + 
            "ORDER BY " + pk + " "+order3+") t3 " +
            "ORDER BY " + orderColumn + " " + order;     
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        Class c = null;
        try
        {
            c= Class.forName(classPath);
        }
        catch (ClassNotFoundException e2)
        {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        }
		try {
			while (rs!=null && rs.next()) {
                
                Object obj = null;
              try
                {
                    obj = c.newInstance();
                }
                catch (Exception e1)
                {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                
          
                for(Method m : setMs){
                    field = m.getName().length()>3 ? m.getName().substring(3, m.getName().length()).toLowerCase()
                                : m.getName().replace("set", "");
                    if(m.getParameterTypes().length > 0){
                        paramType = m.getParameterTypes()[0]+"";
                        if(paramType.contains("int")){
                            try
                            {
                                m.invoke(obj, rs.getInt(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("double")){
                            try
                            {
                                m.invoke(obj, rs.getDouble(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("float")){
                            try
                            {
                                m.invoke(obj, rs.getFloat(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("String")){
                            
                            try
                            {
                                if(rs.getString(field) != null){
                                    m.invoke(obj, rs.getString(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Date")){
                            
                            try
                            {
                                if(rs.getDate(field) != null){
                                	m.invoke(obj, rs.getTimestamp(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Long")){
                            try
                            {
                                m.invoke(obj, rs.getLong(field));   
                            }catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("long")){
                            try
                            {
                                m.invoke(obj, rs.getLong(field));   
                            }catch (Exception e)
                            {
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Double")){
                        	try
                            {
                            	m.invoke(obj, rs.getDouble(field));  
                            }catch (Exception e)
                            {
                                e.printStackTrace();
                            }
                        }else{
                            System.out.println("MSSQLUtil.list()=>未处理的参数类型:"+paramType);
                        }
                    }
                }
                list.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return list;
	}
	
	/**列出所有
	 * @param classPath
	 * @param sql
	 * @return
	 */
	public static List<?> listAll(String classPath, String sql) {
		List list = new ArrayList();
        Method setMs[] = MethodUtil.getBeanMethods(classPath, "set");
        String field = "";
        String paramType = "String";
        if(sql==null){
        	return null;
        }
		String table = classPath.contains(".") ? classPath.substring(classPath
				.lastIndexOf(".") + 1, classPath.length()) : classPath;
		if(sql.startsWith("where")){
			sql = "select * from "+table+" "+sql;
		}
		Connection conn = JdbcTemple.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        Class c = null;
        try
        {
            c= Class.forName(classPath);
        }
        catch (ClassNotFoundException e2)
        {
            // TODO Auto-generated catch block
            e2.printStackTrace();
        }
		try {
			while (rs.next()) {
                
                Object obj = null;
              try
                {
                    obj = c.newInstance();
                }
                catch (Exception e1)
                {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                
          
                for(Method m : setMs){
                    field = m.getName().length()>3 ? m.getName().substring(3, m.getName().length()).toLowerCase()
                                : m.getName().replace("set", "");
                    if(m.getParameterTypes().length > 0){
                        paramType = m.getParameterTypes()[0]+"";
                        if(paramType.contains("int")){
                            try
                            {
                                m.invoke(obj, rs.getInt(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("double")){
                            try
                            {
                                m.invoke(obj, rs.getDouble(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("float")){
                            try
                            {
                                m.invoke(obj, rs.getFloat(field));
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("String")){
                            
                            try
                            {
                                if(rs.getString(field) != null){
                                    m.invoke(obj, rs.getString(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Date")){
                            
                            try
                            {
                                if(rs.getDate(field) != null){
                                	m.invoke(obj, rs.getTimestamp(field));
                                }
                            }
                            catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Long")){
                            try
                            {
                                m.invoke(obj, rs.getLong(field));   
                            }catch (Exception e)
                            {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("long")){
                            try
                            {
                                m.invoke(obj, rs.getLong(field));   
                            }catch (Exception e)
                            {
                                e.printStackTrace();
                            }
                        }else if(paramType.contains("Double")){
                        	try
                            {
                            	m.invoke(obj, rs.getDouble(field));  
                            }catch (Exception e)
                            {
                                e.printStackTrace();
                            }
                        }else{
                            System.out.println("MSSQLUtil.listAll()=>未处理的参数类型:"+paramType);
                        }
                    }
                }
                list.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcTemple.close(conn, pstmt, rs);
		return list;
	}
}
